<?php

namespace App\Http\Model;

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;

class Union extends Base
{
    protected $table = "union";
    protected $user_table = "union_user";
    protected $apply_user_table = "union_user_apply";
    protected $room_table = "union_room";
    protected $room = "room";
    protected $table_union_consume = 'union_consume';
    protected $union_uuid_key = "UNION:UID:GENERATED:DB";
    protected $union_supply_total = "UNION:TOTAL:SUPPLY";
    protected $kick_user_to_union_key = "UNION:USER:KICK";
    protected $table_diamond_rank = 'union_diamond_rank';
    protected $table_union_notice = 'union_notice';
    protected $table_union_rank = "union_rank";
    protected $unionRoomStreamTable = "finance_union_room_stream";
    protected $unionRoomStreamTableDays = "finance_union_room_stream_days";
    protected $unionSettleTable = 'union_settle';

    protected $redis_union_user_consume = 'UNION:USER:CONSUME';
    protected $redis_union_create_times = 'UNION:CREATE:TIMES:';
    protected $redis_union_update_name = 'UNION:UPDATE:NAME:';
    protected $redis_union_dissolve_code = 'UNION:DISSOLVE:CODE:';
    protected $redis_union_update_vest = 'UNION:UPDATE:VEST:';
    protected $redis_union_wealth_level = 'UNION:WEALTH:LEVEL';  //公会财富等级
    protected $redis_union_active_wealth = 'UNION:ACTIVE:WEALTH'; //公会活跃值
    protected $redis_get_union_roomIds = 'UNION:ROOMIDS:'; //公会房间ids
    protected $redis_get_Hour_UnionBill = 'UNION:HOURS:BILL:'; //公会房间小时流水
    protected $redis_get_Day_UnionBill = 'UNION:DAY:BILL:'; //公会房间日流水
    protected $adapt_size;

    protected $redis_invite_join = 'UNION:INVITE:JOIN:';

    public function __construct(array $attributes = [])
    {
        parent::__construct($this->table, $attributes);
        $adapters = explode(',', Redis::HGET('IMAGE:ADAPT', 'union'));
        $this->adapt_size = !empty($adapters) && count($adapters) > 1 ? $adapters[1] : 480;
    }

    //创建公会
    public function createUnion($data = [], $cms = true)
    {
        write_log('updateUnion', 'createUnion', func_get_args());
        if ($cms) {
            $uuid = Redis::LPOP($this->union_uuid_key);
            if ($uuid) {
                $data['uuid'] = $uuid;
                return DB::table($this->table)->insertGetId($data);
            }
            return false;
        }
        $times = Redis::get($this->redis_union_create_times . date('Ymd') . ':' . $data['uuid']) ?? 0;
        if ($times >= 3) {
            return -1;
        }
        $res = DB::table($this->table)->insertGetId($data);
        if ($res) {
            Redis::setex($this->redis_union_create_times . date('Ymd') . ':' . $data['uuid'], strtotime(date('Y-m-d', strtotime('+1 day'))) - time() - 1, $times + 1);
            return $res;
        }
        return 0;
    }

    public function editUnion($union_id, $data = [])
    {
        $where['id'] = $union_id;
        write_log('updateUnion', 'editUnion', func_get_args());
        return DB::table($this->table)->where($where)->update($data);
    }

    /**
     * @param $union_id
     * @param array $data
     * @return bool
     */
    public function updateUnion($union_id, $data = [])
    {
        write_log('updateUnion', 'updateUnion', [$union_id, $data]);
        DB::beginTransaction();
        try {
            if (isset($data['authentication_status']) && $data['authentication_status']==0) {
                $userUnion = DB::table($this->user_table)
                    ->where('un_id', $union_id)
                    ->select('user_id')
                    ->get();
                if ($userUnion) {
                    $userUnionArr = json_decode(json_encode($userUnion), true);
                    $user_id = array_column($userUnionArr, 'user_id');
                    DB::table($this->room)->whereIn('user_id', $user_id)->update(['certified' => 0]);
                }
            }
            DB::table($this->table)->where('id', $union_id)->update($data);
            DB::commit();
            return true;
        } catch (\Exception $exception) {
            DB::rollback();
            Log::info(__METHOD__ . ' has error:' . $exception->getMessage());
            return false;
        }
    }


    //添加公会房间
    public function joinUnionRoom($data)
    {
        return DB::table($this->room_table)->insertGetId($data);
    }

    public function getUnionList($input)
    {
        $search = $input['search'] ?? '';
//        $where[] = ['rule', '=', '1'];
        $where[] = ['stop', '=', '1'];
        $whereOr = $where;
        if ($search && $search != 'search-new') {
            $where[] = ['uuid', 'like', "%$search%"];
            $whereOr[] = ['name', 'like', "%$search%"];
        }
        if (!empty($input['is_sign'])) {
            $whereOr[] = ['authentication_status', '>=', 1];
        }
        $res = DB::table($this->table)->where($where)->orWhere($whereOr)->orderBy('sort', 'desc')->get();
        $res = $this->objToArray($res);
        if ($search && $search != 'search-new') {
            return $res;
        }
        if ($search == 'search-new') {
            shuffle($res);
        }
        return array_slice($res, 0, 5);
    }


    //验证用户是否被踢120h以内
    public function checkUserDelTime($user_id)
    {
        $key = $this->kick_user_to_union_key . $user_id;
        return Redis::get($key);
    }

    //踢出用户
    public function kickUser($user_id)
    {
        $key = $this->kick_user_to_union_key . $user_id;
        return Redis::SETEX($key, 432000, time());
    }


    /*
     * 用户申请记录
     * */
    public function applyUserLog($user_id, $union_id = '')
    {
        $where['user_id'] = $user_id;
        if ($union_id) $where['un_id'] = $union_id;
        $res = DB::table($this->apply_user_table)->where($where)->orderBy('id', 'desc')->get();
        return $this->objToArray($res);
    }

    //获取公会信息
    public function getUnionInfo($union_id)
    {
        $where['stop'] = 1;
        $where['id'] = $union_id;
        $res = DB::table($this->table)->where($where)->first();
        return $this->objToArray($res);
    }

    //获取公会信息
    public function getUnionByUnionId($unionId)
    {
        $res = DB::table($this->table)->where('id', $unionId)->first();
        return $this->objToArray($res);
    }

    //获取公会信息
    public function getUnionInfoToUnUserId($user_id)
    {
        if (is_array($user_id)) {
            $res = DB::table($this->table)->whereIn('un_user_id', $user_id)->first();
            return $this->objToArray($res);
        }
        $where['un_user_id'] = $user_id;
        $res = DB::table($this->table)->where($where)->first();
        return $this->objToArray($res);
    }

    /* 用户公会
     * $user_id 用户id
     * $union_id  公会id
     * */
    public function applyUnionToUser($user_id, $union_id)
    {
        $data['user_id'] = $user_id;
        $data['un_id'] = $union_id;
        return DB::table($this->apply_user_table)->insertGetId($data);
    }


    /*
     * 获取公会对应房间id
     * $union_id  公会id
     * */
//    public function getUnionRoomIds($union_id,$page,$limit)
    public function getUnionRoomIds($union_id)
    {
            $where['un_id'] = $union_id;
            $res = DB::table($this->room_table)->where($where)
                ->leftJoin("room", "room.room_id", "=", "union_room.room_id")
                ->select('union_room.*', 'room_uid', 'name', 'image', 'business_type')
                ->get();
            $ress=$this->objToArray($res);
        return $ress;
    }

    public function getUnionRoomIdsList($union_id, $startDay, $endDay)
    {
        $count = "SELECT a.*,SUM(a.data_value * a.data_num) as total,b.name,b.image,b.room_uid,b.business_type FROM zb_finance_union_room_stream  a LEFT JOIN zb_room  b on a.room_id =b.room_id  WHERE a.union_id=$union_id and a.created_at >='" . $startDay . ' 00:00:00' . "' and a.created_at <='" . $endDay . ' 23:59:59' . "'  GROUP BY a.room_id";
        $res = DB::select($count);
        $ress = $this->objToArray($res);

        return $ress;
    }

    public function getUnionRoomIdsListToday($union_id, $startDay, $endDay)
    {
        $today = date('Y-m-d');
        $count = "SELECT a.*,SUM(a.data_value * a.data_num) as total,b.name,b.image,b.room_uid,b.business_type FROM zb_finance_union_room_stream  a LEFT JOIN zb_room  b on a.room_id =b.room_id  WHERE a.union_id=$union_id and a.created_at >='" . $today . ' 00:00:00' . "' and a.created_at <='" . $today . ' 23:59:59' . "'  GROUP BY a.room_id";
//        echo($count);die;
        $res = DB::select($count);
        $ress = $this->objToArray($res);

        return $ress;
    }

    public function getUnionRoomIdsListDays($union_id, $startDay, $endDay)
    {
        $count = "SELECT id,room_id,union_id,sum(total) as total,created_at FROM zb_finance_union_room_stream_days  a WHERE a.union_id=$union_id and a.created_at >='" . $startDay . ' 00:00:00' . "' and a.created_at <='" . $endDay . ' 23:59:59' . "' GROUP BY a.room_id";
        $res = DB::select($count);
        $ress = $this->objToArray($res);
        foreach ($ress as &$v) {
            $v['total'] = (string)$v['total'];
        }
        unset($v);
        return $ress;
    }
    public function getUnionRoomIdsV2($union_id,$page,$limit)
    {
        $where['un_id'] = $union_id;
        $res = DB::table($this->room_table)->where($where)
            ->leftJoin("room", "room.room_id", "=", "union_room.room_id")
            ->select('union_room.*', 'room_uid', 'name', 'image', 'business_type')
            ->offset($page * $limit)
            ->limit($limit)
            ->get();
        $ress=$this->objToArray($res);
        return $ress;
    }
    /*
     * 根据房间id获取对应公会
     * */
    public function getUnionByRoomId($roomId)
    {
        $where['room_id'] = $roomId;
        $res = DB::table($this->room_table)->where($where)
            ->leftJoin("union", "union.id", "=", "union_room.un_id")
            ->select('union.*')
            ->first();
        return $this->objToArray($res);
    }

    /*
     * 根据用户id获取对应公会Id
     * */
    public function getUnionIdByUserId($userId)
    {
        $res = DB::table($this->user_table)->where('user_id', $userId)->first();
        if (empty($res)) {
            return 0;
        } else {
            return $res->un_id;
        }
    }

    /*
     * 删除公会
     * $user_id  会长id
     * $union_id  公会id
     * */
    public function delUnion($union_id, $user_id)
    {
        $where['id'] = $union_id;
        $where['un_user_id'] = $user_id;
        return DB::table($this->table)->where($where)->delete();
    }


    //更新rule
    public function updateRule($union_id)
    {
        $where['id'] = $union_id;
        $data['rule'] = 1;
        return DB::table($this->table)->where($where)->update($data);
    }
    public function updateUnionRole($union_id,$user_id,$role_id)
    {
        $where['user_id'] = $user_id;
        $where['un_id'] = $union_id;
        $data['role_id'] =$role_id;
        return DB::table('union_user')->where($where)->update($data);
    }
    public function checkUnionRoom($room_id)
    {
        $where['room_id'] = $room_id;
        $res = DB::table($this->room_table)->where($where)->first();
        return $this->objToArray($res);
    }

    public function delUnionRoom($union_id)
    {
        $where['un_id'] = $union_id;
        $res = DB::table($this->room_table)->where($where)->delete();
        return $this->objToArray($res);
    }

    public function getUnionRank($total)
    {
        $sql = "select * from zb_union_rank where start_value <= $total and end_value >=$total";
        $res = DB::select($sql);
        return $this->objToArray($res);

    }

    public function searchUnion($keyWords, $page, $limit)
    {
        $db = DB::table('union')
            ->where('uuid', '=', $keyWords)
            ->orWhere('name', 'like', "%$keyWords%");

        // 总条数
        $totalCount = $db->count('id');

        // 总数
        $result = $db->offset($page * $limit)
            ->limit($limit)
            ->get(['id', 'uuid', 'portrait', 'name', 'notice', 'desc', 'liang']);
        $result = $this->objToArray($result);
        foreach ($result as $k => $v) {
            $result[$k]['portrait'] = getPicDomain() . $v['portrait'];
        }
        return $this->checkPageData($page, $limit, 0, $result);

    }


    public function supplyUnionTotal($union_id)
    {
        $total = Redis::HGET($this->union_supply_total, $union_id) ?? 0;
        return $total;
    }

    /**获取用户在公会的花费
     * @param $union_id
     * @param $user_id
     * @return array|mixed
     */
    public function getUserConsume($union_id, $user_id)
    {
        $res = Redis::HGET($this->redis_union_user_consume, $union_id . "_" . $user_id);
        if (empty($res)) {
            $data['un_id'] = $union_id;
            $data['user_id'] = $user_id;
            $data['diamond'] = 0;
            $data['rank_id'] = 0;
            $data['rank_name'] = '';
            $data['rank_icon'] = '';
            $data['vip_image'] = '';
            $data['visit_image'] = '';
            $data['visit_image_double'] = '';
            $data['visit_image_card'] = '';
            $consume = DB::table($this->table_union_consume)
                ->where(['un_id' => $union_id, 'user_id' => $user_id])
                ->first();
            $union = $this->getUnionInfo($union_id);
            $data['union_name'] = $union['name'] ? $union['name'] : '';
            $data['union_portrait'] = $union['portrait'] ? build_image($union['portrait']) : '';
            if (!empty($consume)) {
                $data['diamond'] = $consume->diamond;
                $user_rank = DB::table($this->table_diamond_rank)
                    ->where('min_value', '<=', $consume->diamond)
                    ->where('max_value', '>', $consume->diamond)
                    ->first();
                if (!empty($user_rank)) {
                    $data['rank_id'] = $user_rank->rank_id;
                    $data['rank_name'] = $user_rank->rank_name;
                    $data['rank_icon'] = build_image($user_rank->rank_icon);
                    $data['vip_image'] = build_image($user_rank->vip_image);
                    $data['visit_image'] = build_image($user_rank->visit_image);
                    $data['visit_image_double'] = build_image($user_rank->visit_image_double);
                    $data['visit_image_card'] = build_image($user_rank->visit_image_card);
                }
            }
            Redis::HSET($this->redis_union_user_consume, $union_id . "_" . $user_id, json_encode($data));
            return $data;
        }
        return !empty($res) ? json_decode($res, true) : [];
    }

    /**获取钻石排行
     * @return mixed
     */
    public function getDiamondRank()
    {
        $res = DB::table($this->table_diamond_rank)->get();
        $res = $this->objToArray($res);
        foreach ($res as &$r) {
            $r['rank_icon'] = !empty($r['rank_icon']) ? getPicDomain() . $r['rank_icon'] : "";
            $r['vip_image'] = !empty($r['vip_image']) ? getPicDomain() . $r['vip_image'] : "";
        }
        return $this->objToArray($res);
    }

    /**根据房间获取公会
     * @param $room_ids
     * @return array
     */
    public function getRoomUnion($room_ids)
    {
        $res = DB::table($this->room_table)->whereIn('room_id', $room_ids)->get(['room_id', 'un_id']);
        $res = $this->objToArray($res);
        $data = [];
        foreach ($res as $r) {
            $data[$r['room_id']] = $r;
        }
        return $data;
    }

    /**定时更新用户花费
     * @param $params
     */
    public function updateUserConsume($params)
    {
        foreach ($params as $k => $v) {
            $union_room_user = explode('_', $k);
            $t = time();
            $sql = "insert into zb_{$this->table_union_consume} (`un_id`,`user_id`,`diamond`,`change_timestamp`) values ({$union_room_user[0]},{$union_room_user[1]} ,{$v} ,{$t}) on duplicate key update diamond = diamond+{$v}, change_timestamp={$t}";
            DB::INSERT($sql);
            Redis::HDEL($this->redis_union_user_consume, $union_room_user[0] . '_' . $union_room_user[1]);
        }
    }

    /**统计用户在所有公会花费钻石数
     * @param $user_id
     * @return mixed
     */
    public function getAllConsumeByUserId($user_id)
    {
        $res = DB::table($this->table)
            ->leftJoin("union_consume", "union.id", "=", "union_consume.un_id")
            ->where('union_consume.user_id', $user_id)
            ->orderBy('union_consume.diamond', 'desc')
            ->get(['union_consume.*']);
        return $this->objToArray($res);
    }

    /**根据用户id获取所属公会
     * @param $user_ids
     * @return array
     */
    public function getUserUnion($user_ids)
    {
        $res = DB::table($this->user_table)->whereIn('user_id', $user_ids)->get();
        $res = $this->objToArray($res);
        $data = [];
        if (!empty($res)) {
            foreach ($res as $r) {
                $data[$r['user_id']] = $r;
            }
        }
        return $data;
    }

    //来来语音116号需求-公会体系迭代

    /**判断公会名称是否存在
     * @param $name
     * @return mixed
     */
    public function checkUnionName($name)
    {
        $res = DB::table($this->table)
            ->where('name', $name)
            ->first();
        if (empty($res->id)) {
            $res = DB::table('family')
                ->where('name', $name)
                ->first();
        }
        return $this->objToArray($res);
    }

    /**获取公告列表
     * @param $union_id
     * @param $page
     * @param $limit
     * @return mixed
     */
    public function getNoticeList($union_id, $page, $limit)
    {
        $res = DB::table($this->table_union_notice)
            ->where('union_id', $union_id)
            ->orderBy('id', 'desc')
            ->offset($page * $limit)
            ->limit($limit)
            ->get();
        $result = $this->objToArray($res);
        return $result;
    }

    /**发布公告
     * @param $data
     * @return int
     */
    public function addNotice($data)
    {
        return DB::table($this->table_union_notice)->insertGetId($data);
    }

    /**删除公告
     * @param $id
     * @return int
     */
    public function delNotice($id)
    {
        return DB::table($this->table_union_notice)->delete($id);
    }

    public function setUnionInfo($input, $add_time = true)
    {
        $union_id = $input['union_id'];
        if (isset($input['name'])) {
            $arr['name'] = $input['name'];
        }
        if (isset($input['portrait'])) {
            $arr['portrait'] = $input['portrait'];
        }
        if (isset($input['declaration'])) {
            $arr['declaration'] = $input['declaration'];
        }
        if (isset($input['vest'])) {
            $arr['vest'] = $input['vest'];
        }
        if (!empty($arr)) {
            $res = DB::table($this->table)
                ->where("id", $union_id)
                ->update($arr);
        }
        if (!empty($arr['name']) && $add_time) {
            Redis::SETEX($this->redis_union_update_name . $union_id, 24 * 60 * 60, time());
        }
        return empty($res) ? 0 : $res;
    }

    public function getUpdateNameTime($union_id)
    {
        return Redis::get($this->redis_union_update_name . $union_id);
    }
//    校验会长身份，发送短信验证码
    //redis存储
    public function redisCache($key, $value, $expire_time = 300)
    {
        Redis::setex($key, $expire_time, $value);
        return true;
    }

    //redis存储获取
    public function redisCacheGet($key)
    {
        return Redis::get($key);
    }

    //redis 存储删除
    public function redisCacheDel($key)
    {
        return Redis::del($key);
    }

    /**根据id获取公会信息
     * @param $union_ids
     * @param int $page
     * @param int $limit
     * @return mixed
     */
    public function getUnions($union_ids, $page = 0, $limit = 10)
    {
        if (!empty($union_ids)) {
            $ids_ordered = implode(',', $union_ids);
            $res = DB::table($this->table)->whereIn('id', $union_ids)
                ->orderByRaw(DB::raw("FIELD(id, $ids_ordered)"))
                ->get();
            return $this->objToArray($res);
        }
//        $where[] = ['rule', '=', '1'];
        $where[] = ['stop', '=', '1'];
        $res = DB::table($this->table)->where($where)
            ->offset(($page) * $limit)
            ->limit($limit)
            ->get();
        return $this->objToArray($res);
    }

    /**生成解散码
     * @param $union_id
     * @return string
     */
    public function generateDissolveCode($union_id)
    {
        $code = md5(microtime(true) . rand(1, 99999));
        Redis::SETEX($this->redis_union_dissolve_code . $union_id, 24 * 60 * 60, $code);
        return $code;
    }

    /**获取解散码
     * @param $union_id
     * @return int
     */
    public function getDissolveCode($union_id)
    {
        $code = Redis::get($this->redis_union_dissolve_code . $union_id);
        return empty($code) ? '' : $code;
    }


    public function getAllUnion()
    {
        $res = DB::table($this->table)->where('stop', 1)->get();
        return $this->objToArray($res);
    }

    /**设置马甲更新时间
     * @param $union_id
     */
    public function setVestTime($union_id)
    {
        Redis::SETEX($this->redis_union_update_vest . $union_id, 24 * 60 * 60, time());
    }

    /**获取马甲更新时间
     * @param $union_id
     * @return mixed
     */
    public function getVestTime($union_id)
    {
        return Redis::get($this->redis_union_update_vest . $union_id);
    }

    /**
     * 获取公会财富等级与活跃等级
     * @param $unionId  公会ID
     */
    public function getGrade($unionId)
    {
        $data['rankVal'] = "等级一";
        $data['rankActiveVal'] = "等级一";

        //财富等级
        $key = $this->redis_union_wealth_level;
        $rankVal = Redis::hget($key, $unionId);
        if (!empty($rankVal)) {
            $unionRank = DB::table($this->table_union_rank)->where(['id' => $rankVal])->select('name')->first();
            $data['rankVal'] = $unionRank->name ?? "";
        }
        //活跃等级
        $activeKey = $this->redis_union_active_wealth;
        $rankActiveVal = Redis::hget($activeKey, $unionId);
        if (!empty($rankActiveVal)) {
            $results = DB::select('select * from zb_union_active_rank where ' . $rankActiveVal . ' between `start_value` and `end_value`');
            $data['rankActiveVal'] = $results[0]->name ?? "";
        }
        return $data;

    }

    /**压缩图片
     * @param $image
     * @return array|string
     */
    public function buildUnionImage($image)
    {
        return build_adapt_images($image, $this->adapt_size);
    }

    /**添加邀请记录
     * @param $union_id
     * @param $invite_user_id
     */
    public function setUnionInvite($union_id, $invite_user_id)
    {
        Redis::HSET($this->redis_invite_join . $union_id, $invite_user_id, time());
    }

    /**获取邀请用户记
     * @param $union_id
     * @param $invite_user_id
     * @return mixed
     */
    public function getUnionInvite($union_id, $invite_user_id)
    {
        return Redis::HGET($this->redis_invite_join . $union_id, $invite_user_id) ?? '';
    }

    /**删除邀请记录
     * @param $union_id
     * @param $invite_user_id
     */
    public function delUnionInvite($union_id, $invite_user_id)
    {
        Redis::HDEL($this->redis_invite_join . $union_id, $invite_user_id);
    }

    /**获取申请记录
     * @param $union_id
     * @param $page
     * @param $pageSize
     * @return mixed
     */
    public function getInviteLog($union_id, $page, $pageSize)
    {
        $res = DB::table($this->apply_user_table)
            ->where('un_id', $union_id)
            ->offset(($page) * $pageSize)
            ->limit($pageSize)
            ->get();
        return $this->objToArray($res);
    }

    /**获取工会申请记录
     * @param $union_id
     * @param $page
     * @param $pageSize
     * @return mixed
     */
    public function getInviteUnionLog($union_id)
    {
        $res = DB::table($this->apply_user_table)
            ->where('un_id', $union_id)
            ->where('status',0)
            ->get();
        return $this->objToArray($res);
    }
    public function countInviteUnionLog($union_id)
    {
        $res = DB::table($this->apply_user_table)
            ->where('un_id', $union_id)
            ->where('status',0)
            ->count();
        return $res;
    }

    public function isUnionUser($union_id, $uid)
    {
        $res = DB::table($this->user_table)
            ->where('user_id', $uid)
            ->where('un_id', $union_id)
            ->first();
        return !empty($res);
    }

    /**
     * 新增公会房间流水
     */
    public function adduUnionRoomStream($data)
    {
        return DB::table($this->unionRoomStreamTable)->insertGetId($data);
    }

    /**
     * 获取公会结算流水
     * @param array $unionuser 主播数组
     * @param $startDate 开始日期
     * @param $endDate 结束日期
     * @return array
     */
    public function showUnionRoomBill($startDate, $endDate, $roomId, $union_id)
    {
        $startDate = Carbon::parse($startDate)->startOfDay();//开始时间 00:00:00
        $endDate = Carbon::parse($endDate)->endOfDay();//结束时间23:59:59
        if (empty($roomId))
        {
            $allRoom = $this->getUnionRoomIds($union_id);
            $allRoom = array_column($allRoom,'room_id');
            $count = DB::table($this->unionRoomStreamTable)
                ->whereIn('room_id',$allRoom)
                ->whereBetween('created_at',[$startDate,$endDate])
                ->select(DB::raw('SUM(data_value * data_num) AS total'))
                ->get();
        }else{
            $count = DB::table($this->unionRoomStreamTable)
                ->where('room_id',$roomId)
                ->whereBetween('created_at',[$startDate,$endDate])
                ->select(DB::raw('SUM(data_value * data_num) AS total'))
                ->get();
        }

        return $this->objToArray($count);
    }
    /*
     * 获取用户对应对公结算公会的会长ID
     * */
    public function getSettleUnionUserIdByUserId($userId)
    {
        if(empty($userId)) {
            return 0;
        }
        $unionUser = DB::table($this->user_table)
            ->where('user_id', $userId)
            ->first();
        $unionUser = $this->objToArray($unionUser);
        if(empty($unionUser) || empty($unionUser['un_id'])) {
            return 0;
        }

        $union = DB::table($this->table)
            ->where('id', $unionUser['un_id'])
            ->first();
        $union = $this->objToArray($union);
        if(empty($union) || empty($union['un_user_id'])) {
            return 0;
        }
        if($union['settle_type'] != 2) {
            return 0;
        }
        if(!empty($union['un_user_id'])) {
            return (int) $union['un_user_id'];
        }
        return 0;
    }

    //根据用户id获取所属认证公会
    public function getUserUnionInfo($userId)
    {
        if(empty($userId)) {
            return [];
        }
        $data = DB::table($this->user_table.' as a')
            ->join($this->table.' as b', 'b.id', '=', 'a.un_id')
            ->where('a.user_id', $userId)
            ->where('b.authentication_status', '>', 0)
            ->where('b.stop', 1)
            ->select('a.user_id', 'b.id', 'b.un_user_id', 'b.name')
            ->first();
        $data = self::objToArray($data);
        if(!empty($data['user_id']) && !empty($data['id'])) {
            return $data;
        }

        return [];
    }


    /**
     * 按小时获取房间结算流水
     */
    public function getHourRoomBill($roomId, $date){
        $startDate = Carbon::parse($date)->startOfDay(); //date 格式 Y-m-d H:i:s
        $endDate = Carbon::parse($date)->endOfDay();
        $data = DB::table($this->unionRoomStreamTable)
        ->selectRaw(DB::raw("room_id,DATE_FORMAT(created_at, '%H' ) as time,
        SUM(data_value * data_num) as total "))
        ->where('room_id',$roomId)
        ->where('created_at','>=',$startDate)
        ->where('created_at','<=',$endDate)
        ->groupBy('time')
        ->get();
        $data = self::objToArray($data);
        return $data;
    }

    /**
     * 按小时获取房间结算流水
     */
//    $list = [];
//    if($dm == 'm') {
//            $starttime = strtotime(date('Y-m-01', $starttime));
//            while ($starttime <= $endtime) {
//                $list[] = date('Y-m', $starttime);
//                $starttime = strtotime('+1 month', $starttime);
//            }
//            $format = '%Y-%m';
//        }

    public function getdayRoomBill($roomId,$startDate,$endDate){
        $startDate = Carbon::parse($startDate)->startOfDay(); //date 格式 Y-m-d H:i:s
        $endDate = Carbon::parse($endDate)->endOfDay();
        $data = DB::table($this->unionRoomStreamTable)
            ->selectRaw(DB::raw("room_id,DATE_FORMAT(created_at, '%m-%d' ) as time,
        SUM(data_value * data_num) as total "))
            ->where('room_id',$roomId)
            ->where('created_at','>=',$startDate)
            ->where('created_at','<=',$endDate)
            ->groupBy('time')
            ->get();
        $data = self::objToArray($data);
        return $data;
    }
    public function getdayRoomBillDays($roomId,$startDate,$endDate){
//        $startDate = Carbon::parse($startDate)->startOfDay(); //date 格式 Y-m-d H:i:s
//        $endDate = Carbon::parse($endDate)->endOfDay();
        $startDate=Date("Y-m-d",strtotime($startDate));
        $endDate=Date("Y-m-d",strtotime($endDate));
        $data = DB::table($this->unionRoomStreamTableDays)
            ->selectRaw(DB::raw("room_id,DATE_FORMAT(created_at, '%Y-%m-%d' ) as time,
        SUM(total) as total "))
            ->where('room_id',$roomId)
            ->where('created_at','>=',$startDate)
            ->where('created_at','<=',$endDate)
            ->groupBy('time')
            ->get();
        $data = self::objToArray($data);
        return $data;
    }

    public function getdayRoomBillToday($roomId,$startDate,$endDate){
        $today=date('Y-m-d');
        $data = DB::table($this->unionRoomStreamTable)
            ->selectRaw(DB::raw("room_id,DATE_FORMAT(created_at, '%Y-%m-%d' ) as time,
        SUM(data_value * data_num) as total"))
            ->where('room_id',$roomId)
            ->where('created_at', '>=', $today . ' 00:00:00')
            ->where('created_at', '<=', $today. ' 23:59:59')
            ->groupBy('time')
            ->get();
        $data = self::objToArray($data);
        return $data;
    }

    /**
     * 按小时获取工会结算流水
     */
    public function getHourUnionBill($union_id, $date){
        $startDate = Carbon::parse($date)->startOfDay(); //date 格式 Y-m-d H:i:s
        $endDate = Carbon::parse($date)->endOfDay();
//        $key = $this->redis_get_Hour_UnionBill . $union_id;
//        $res= Redis::get($key);
//        if(empty($res)){
            $data = DB::table($this->unionRoomStreamTable)
                ->selectRaw(DB::raw("union_id,DATE_FORMAT(created_at, '%H' ) as time,
        SUM(data_value * data_num) as total "))
                ->where('union_id',$union_id)
                ->where('created_at','>=',$startDate)
                ->where('created_at','<=',$endDate)
                ->groupBy('time')
                ->get();
            $res = self::objToArray($data);
//            if(!empty($res)) {
//                Redis::setex($key, 3600, json_encode($res));
//            }
//        }else{
//            $res=json_decode($res,true);
//        }
        return $res;
    }
//    public function getHourUnionBillDays($union_id, $date){
//        $startDate = Carbon::parse($date)->startOfDay(); //date 格式 Y-m-d H:i:s
//        $endDate = Carbon::parse($date)->endOfDay();
////        $key = $this->redis_get_Hour_UnionBill . $union_id;
////        $res= Redis::get($key);
////        if(empty($res)){
//            $data = DB::table($this->unionRoomStreamTableDays)
//                ->selectRaw(DB::raw("union_id,DATE_FORMAT(created_at, '%H' ) as time,sum(total) as total "))
//                ->where('union_id',$union_id)
//                ->where('created_at','>=',$startDate)
//                ->where('created_at','<=',$endDate)
//                ->groupBy('time')
//                ->get();
//            $res = self::objToArray($data);
////            if(!empty($res)) {
////                Redis::setex($key, 3600, json_encode($res));
////            }
////        }else{
////            $res=json_decode($res,true);
////        }
//        return $res;
//    }
    public function getDayUnionBill($union_id,$startDate,$endDate){
//        $key = $this->redis_get_Hour_UnionBill . $union_id;
//        $res= Redis::get($key);
//        if(empty($res)) {
            if (empty($startDate)) {
                $data = DB::table($this->unionRoomStreamTable)
                    ->selectRaw(DB::raw("union_id,DATE_FORMAT(created_at, '%m-%d' ) as time,
            SUM(data_value * data_num) as total "))
                    ->where('union_id', $union_id)
                    ->groupBy('time')
                    ->orderby('time')
                    ->get();
            } else {
                $startDate = Carbon::parse($startDate)->startOfDay(); //date 格式 Y-m-d H:i:s
                $endDate = Carbon::parse($endDate)->endOfDay();
                $data = DB::table($this->unionRoomStreamTable)
                    ->selectRaw(DB::raw("union_id,DATE_FORMAT(created_at, '%m-%d' ) as time,
            SUM(data_value * data_num) as total "))
                    ->where('union_id', $union_id)
                    ->where('created_at', '>=', $startDate)
                    ->where('created_at', '<=', $endDate)
                    ->groupBy('time')
                    ->orderby('time')
                    ->get();
            }
            $res = self::objToArray($data);
//            if(!empty($res)) {
//                Redis::setex($key, 3600, json_encode($res));
//            }
//        }else{
//            $res=json_decode($res,true);
//        }
        return $res;
    }


    public function getDayUnionBillToday($union_id,$startDate,$endDate){
        if (empty($startDate)) {
            $data = DB::table($this->unionRoomStreamTable)
                ->selectRaw(DB::raw("union_id,room_id,DATE_FORMAT(created_at, '%Y-%m-%d' ) as time,
            SUM(data_value * data_num) as total "))
                ->where('union_id', $union_id)
                ->groupBy('room_id')
                ->groupBy('time')
                ->orderby('time')
                ->get();
        } else {
            $today=date('Y-m-d');
            $data = DB::table($this->unionRoomStreamTable)
                ->selectRaw(DB::raw("union_id,room_id,DATE_FORMAT(created_at, '%Y-%m-%d' ) as time,
            SUM(data_value * data_num) as total "))
                ->where('union_id', $union_id)
                ->groupBy('room_id')
                ->where('created_at', '>=', $today . ' 00:00:00')
                ->where('created_at', '<=', $today. ' 23:59:59')
                ->groupBy('time')
                ->orderby('time')
                ->get();
        }
        $res = self::objToArray($data);
        return $res;
    }

    public function getDayUnionBillDays($union_id,$startDate,$endDate){
//        $key = $this->redis_get_Hour_UnionBill . $union_id;
//        $res= Redis::get($key);
//        if(empty($res)) {
            if (empty($startDate)) {
                $data = DB::table($this->unionRoomStreamTableDays)
                ->selectRaw(DB::raw("union_id,room_id,DATE_FORMAT(created_at, '%Y-%m-%d' ) as time,sum(total) as total"))
                ->where('union_id', $union_id)
                ->groupBy('room_id')
                ->groupBy('time')
                ->orderby('time')
                ->get();
            } else {
                $startDate = date('Y-m-d', strtotime($startDate)); //date 格式 Y-m-d H:i:s
                $endDate = date('Y-m-d', strtotime($endDate));
                $data = DB::table($this->unionRoomStreamTableDays)
                    ->selectRaw(DB::raw("union_id,room_id,DATE_FORMAT(created_at, '%Y-%m-%d' ) as time,sum(total) as total"))
                    ->where('union_id', $union_id)
                    ->where('created_at', '>=', $startDate)
                    ->where('created_at', '<=', $endDate)
                    ->groupBy('room_id')
                    ->groupBy('time')
                    ->orderby('time')
                    ->get();
            }
            $res = self::objToArray($data);
//            if(!empty($res)) {
//                Redis::setex($key, 3600, json_encode($res));
//            }
//        }else{
//            $res=json_decode($res,true);
//        }
        return $res;
    }

    /**
     * 公会总流水
     */
    public function getUnionFinanceALL($union_id){
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('union_id', $union_id)
                ->first(['total']);
        $data = self::objToArray($data);
        return $data['total'];

    }

    /**
     * 公会历史总流水
     */
    public function getUnionFinanceALLHistory($union_id){
        $data = DB::table($this->unionRoomStreamTableDays)
            ->selectRaw(DB::raw("SUM(total) as total "))
            ->where('union_id', $union_id)
            ->first(['total']);
        $data = self::objToArray($data);
        return $data['total'];

    }

    public function getUnionFinanceALLV2($union_id,$startDay,$endDay){
        if(empty($startDay)) {
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('union_id', $union_id)
                ->first(['total']);

        }else{
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('created_at','>=',$startDay.' 00:00:00')
                ->where('created_at','<=',$endDay.' 23:59:59')
                ->where('union_id', $union_id)
                ->first(['total']);
        }
        $data = self::objToArray($data);
        return $data['total'];
    }

    public function getUnionFinanceALLV2Today($union_id,$startDay,$endDay){
        if(empty($startDay)) {
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('union_id', $union_id)
                ->first(['total']);

        }else{
            $today = date('Y-m-d');
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('created_at','>=',$today.' 00:00:00')
                ->where('created_at','<=',$today.' 23:59:59')
                ->where('union_id', $union_id)
                ->first(['total']);
        }
        $data = self::objToArray($data);
        return $data['total'];
    }

    public function getUnionFinanceALLV2Days($union_id,$startDay,$endDay){
        if(empty($startDay)) {
            $data = DB::table($this->unionRoomStreamTableDays)
                ->selectRaw(DB::raw("sum(total) as total"))
                ->where('union_id', $union_id)
                ->first(['total']);

        }else{
            $data = DB::table($this->unionRoomStreamTableDays)
                ->selectRaw(DB::raw("SUM(total) as total"))
                ->where('created_at','>=',$startDay.' 00:00:00')
                ->where('created_at','<=',$endDay.' 23:59:59')
                ->where('union_id', $union_id)
                ->first(['total']);
        }
        $data = self::objToArray($data);
        return $data['total'];
    }
//    /**
//     * 公会房间流水
//     */
//    public function getUnionRoomFinanceALL($room_id,$startDay,$endDay){
//        if(empty($startDay)){
//            $data = DB::table($this->unionRoomStreamTable)
//                ->whereIn('data_type', [1, 2])
//                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
//                ->where('room_id',$room_id)
//                ->first();
//        }else{
//            $data = DB::table($this->unionRoomStreamTable)
//                ->whereIn('data_type', [1, 2])
//                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
//                ->where('room_id',$room_id)
//                ->where('created_at','>=',$startDay.' 00:00:00')
//                ->where('created_at','<=',$endDay.' 23:59:59')
//                ->first();
//        }
//
//        $data = self::objToArray($data);
//        return $data;
//    }
    /**
     * 公会房间流水
     */
    public function getUnionRoomFinanceALL($room_id,$startDay,$endDay){
        if(empty($startDay)){
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('room_id',$room_id)
                ->first();
        }else{
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('room_id',$room_id)
                ->where('created_at','>=',$startDay.' 00:00:00')
                ->where('created_at','<=',$endDay.' 23:59:59')
                ->first();
        }

        $data = self::objToArray($data);
        return $data;
    }

    /**
     * 公会房间流水
     */
    public function getUnionRoomFinanceTotal($union_id, $room_id, $startDay, $endDay){
        if(empty($startDay)){
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('union_id',$union_id)
                ->where('room_id',$room_id)
                ->first();
        }else{
            $data = DB::table($this->unionRoomStreamTable)
                ->whereIn('data_type', [1, 2])
                ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
                ->where('union_id',$union_id)
                ->where('room_id',$room_id)
                ->where('created_at','>=',$startDay.' 00:00:00')
                ->where('created_at','<=',$endDay.' 23:59:59')
                ->first();
        }

        $data = self::objToArray($data);
        return $data;
    }

    /**
     * 公会人员总流水
     */
    public function getUnionUserFinanceALL($user_id){
        $data = DB::table($this->unionRoomStreamTable)
            ->whereIn('data_type', [1, 2])
            ->selectRaw(DB::raw("SUM(data_value * data_num) as total "))
            ->where('user_id',$user_id)
            ->first(['total']);

        $data = self::objToArray($data);
        return $data;
    }
    /**
     * 获取当前用户房间权限
     * @param $user_id
     * @return int 0:普通用户 1：管理员 2：房主
     */
    public function get_roleV2($user_id,$room_id){
        $result = DB::table("room_role")
            ->where("user_id", $user_id)
            ->where("room_id", $room_id)
            ->first();
        if (empty($result)){
            return 0;
        }
        $result = self::objToArray($result);
        return $result;
    }
    public function get_role($user_id){
        $result = DB::table("room_role")
            ->where("user_id", $user_id)
            ->first();
        if (empty($result)){
            return 0;
        }
        $result = self::objToArray($result);
        return $result;
    }
}
